C# 有split功能,那sql sever呢?
方法1.
--版本 :　SQL SERVER2019
--STRING_SPLIT 需要為至少 130 的相容性層級。

USE AdventureWorks2012;  
GO  
SELECT compatibility_level  
FROM sys.databases WHERE name = 'AdventureWorks2012';  
ALTER DATABASE AdventureWorks2012 SET COMPATIBILITY_LEVEL = 120; 

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

方法2.(網路上找的)

CREATE FUNCTION dbo.SplitString
(
@SplitStr nvarchar(1000),
@SplitChar nvarchar(5)
)
RETURNS @RtnValue table
(
Data nvarchar(500)
)
AS
BEGIN
Declare @Count int
Set @Count = 1

While (Charindex(@SplitChar,@SplitStr)>0)
Begin
Insert Into @RtnValue (Data)
Select
Data = ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1)))

Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr))
Set @Count = @Count + 1
End

Insert Into @RtnValue (Data)
Select Data = ltrim(rtrim(@SplitStr))

Return
END
select * from SplitString('1,22,333,444,5555,666', ',')

方法2變形

CREATE FUNCTION [dbo].[Func_StringSplit] (@STRING NVARCHAR(4000),@SPLIT NVARCHAR(5))
RETURNS @RESULT TABLE(ID INT,WORD NVARCHAR(100))
AS
BEGIN
	DECLARE @I INT
	SET @I = 1
	WHILE (CHARINDEX(@SPLIT,@STRING) <> 0)
		BEGIN
			INSERT INTO @RESULT(ID,WORD) VALUES (@I,SUBSTRING(@STRING,1,CHARINDEX(@SPLIT,@STRING) - 1))
			SET @STRING = STUFF(@STRING,1,CHARINDEX(@SPLIT,@STRING),'') --刪除指定長度的字串符號並在指定的起始點插入另一組字串符號
			SET @I = @I + 1
		END
		INSERT INTO @RESULT(ID,WORD) VALUES (@I,@STRING) --加上最後一個不含分隔的字

	RETURN
END

select * from [Func_StringSplit]('1,22,333,444,5555,666', ',')

ref
https://docs.microsoft.com/zh-tw/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database?view=sql-server-ver15
https://yangxinde.pixnet.net/blog/post/24638424